SET QUOTED_IDENTIFIER OFF
GO
SET ANSI_NULLS ON
GO
CREATE PROCEDURE [dbo].[SP_TCMOVI_BANC_Q21]
/*-------------------------------------------------------*/
/*---Empresa               OFISIS S.A.                --*/
/*---Cliente               SANTILLANA                 --*/
/*---Sistema               Tesoreria                  --*/
/*---M¸dulo                Empresa                    --*/
/*---Programa              FLUJO DE CAJA DETALLADO    --*/
/*---Script                SP_TCMOVI_BANC_Q21.sql     --*/
/*---Nombre SP             SP_TCMOVI_BANC_Q21         --*/
/*---Desarrollado por      Reynaldo Menacho Luyo      --*/
/*---Fecha Creaci¸n        25/09/2001                 --*/
/*---Base Datos            SQL SERVER		      --*/
/*---Versi¸n               2000                       --*/
/*---Invoca a SP                                      --*/
/*-------------------------------------------------------*/

/*-------------------------------------------------------*/
/*---Modificado 1 por     					 --*/
/*---Fecha Modificaci¸n   					 --*/ 
/*---Detalle Modificaci¸n 					 --*/
/*-------------------------------------------------------*/

/*-------------------------------------------------------*/
/*---Drop Proc SP_TCMOVI_BANC_Q21                      --*/
/*---Grant all on SP_TCMOVI_BANC_Q21 to public         --*/ 
/*---SP_TCMOVI_BANC_Q21 '01', '2001/08/31', 'SOL'      --*/
/*-------------------------------------------------------*/

@ISCO_EMPR 	TD_VC_002,
@IDFE_INIC	TD_DT_001,
@IDFE_FINA	TD_DT_001,
@ISCO_MONE      TD_VC_003
	As
CREATE TABLE #TWFLUJ_CAJA_Q01_2
 ( CO_EMPR      varchar(3) NULL,
   CO_ARUB	varchar(3) NULL ,
   CO_RUBR	varchar(3) NULL ,
   CO_SUBR	varchar(3) NULL ,
   FE_OPER	datetime NULL,
   FA_TIPO_CAMB numeric(15,6) DEFAULT 0,
   CO_MONE	varchar(3) NULL ,
   IM_MOVI	numeric(16,4) DEFAULT 0,
   IM_MOVI_CONV numeric(16,4) DEFAULT 0,
   TI_DETA	char(1) NULL,
   NU_COMP_TRAN varchar(8) NULL,
   TI_SITU      varchar(3) NULL,
   DE_OBSE      varchar(100) NULL )

DECLARE 

  @VNFE_0001       TD_IN_001, 
  @VNFE_0002       TD_IN_001,
  @CSCO_EMPR       TD_VC_003, 
  @CSDE_NOMB       TD_VC_100, 
  @CSTI_TRAN       TD_VC_001,
  @CSNU_COMP_BANC  TD_VC_008, 
  @CDFE_OPER       TD_DT_001, 
  @CSDE_OBSE       TD_VC_100, 
  @CSCO_MONE       TD_VC_003, 
  @CNFA_TIPO_CAMB  TD_NU_009_004,
  @CNIM_DETA_NEGA  TD_NU_016_004, 
  @CSTI_SITU       TD_VC_003, 
  @CSNU_CANJ       TD_VC_008, 
  @CSNU_RENO       TD_VC_008,
  @CSCO_TIPO_DOCU  TD_VC_003, 
  @CSNU_DOCU_CLIE  TD_VC_020,
  @VNIM_MOVI       TD_NU_016_004, 
  @VSCO_ARUB       TD_VC_003, 
  @VSDE_ARUB       TD_VC_100, 
  @VSCO_RUBR       TD_VC_003 ,
  @VSDE_RUBR       TD_VC_100, 
  @VSCO_SUBR       TD_VC_003, 
  @VSDE_SUBR       TD_VC_100 ,
  @CNPO_DIST       TD_NU_009_004, 
  @CNNU_ORDE_0001  TD_IN_001, 
  @CNNU_ORDE_0002  TD_IN_001,
  @CNNU_ORDE_0003  TD_IN_001,
  @CSCO_CLIE       TD_VC_020,
  @VNIM_CONV       TD_NU_016_004,
  @CSNU_LETR_CLIE  TD_VC_020

INSERT INTO #TWFLUJ_CAJA_Q01_2
(CO_EMPR, CO_ARUB, CO_RUBR, CO_SUBR, FE_OPER, FA_TIPO_CAMB, CO_MONE, IM_MOVI, TI_DETA, NU_COMP_TRAN, DE_OBSE)
SELECT T1.CO_EMPR , T4.CO_ARUB , T3.CO_RUBR , T2.CO_SUBR , T1.FE_OPER ,  T1.FA_TIPO_CAMB,
T5.CO_MONE , T1.IM_MOVI * PATINDEX(T1.TI_MOVI_BANC, 'I') + T1.IM_MOVI * - PATINDEX(T1.TI_MOVI_BANC, 'E') ,
'B' , T1.NU_COMP_BANC , T1.DE_OBSE
FROM TCMOVI_BANC T1, TTSUBR_FINA T2, TTRUBR_FINA T3, TTARUB_FINA T4, TMCNTA_BANC T5, TMEMPR T6
WHERE 	(( @ISCO_EMPR IS NOT NULL AND T1.CO_EMPR = @ISCO_EMPR) OR @ISCO_EMPR IS NULL)
		AND T1.FE_OPER >= @IDFE_INIC
		AND T1.FE_OPER <= @IDFE_FINA
		AND T2.ST_REPO = 'S'
		AND T1.CO_EMPR = T2.CO_EMPR
		AND T1.CO_SUBR = T2.CO_SUBR
		AND T2.CO_EMPR = T3.CO_EMPR
		AND T2.CO_RUBR = T3.CO_RUBR
		AND T3.CO_EMPR = T4.CO_EMPR
		AND T3.CO_ARUB = T4.CO_ARUB
		AND T1.CO_EMPR = T5.CO_EMPR
		AND T1.CO_BANC = T5.CO_BANC
		AND T1.NU_CNTA_BANC = T5.NU_CNTA_BANC
		AND T1.CO_EMPR = T6.CO_EMPR
		AND EXISTS ( SELECT T7.CO_RUBR
		 		FROM 	TTRUBR_FINA T7
		 		WHERE 	T7.CO_EMPR = T2.CO_EMPR
		 			AND T7.CO_RUBR = T2.CO_RUBR
		 			AND T7.ST_REPO = 'S'
		AND EXISTS ( SELECT 	T8.CO_ARUB
		 	      	FROM    	TTARUB_FINA T8
		 	      	WHERE 	T8.CO_EMPR = T7.CO_EMPR
		 	      		AND T8.CO_ARUB = T7.CO_ARUB
		 	      		AND T8.ST_REPO = 'S'))
Union All
SELECT T1.CO_EMPR , T4.CO_ARUB , T3.CO_RUBR , T2.CO_SUBR , T1.FE_OPER ,  T1.FA_TIPO_CAMB,
T5.CO_MONE , T1.IM_MOVI * PATINDEX(T1.TI_MOVI, 'I') + T1.IM_MOVI * -PATINDEX(T1.TI_MOVI, 'E') ,
'B' , T1.NU_COMP_CAJA , T1.DE_OBSE 
FROM TCMOVI_CAJA T1, TTSUBR_FINA T2, TTRUBR_FINA T3, TTARUB_FINA T4, TMCAJA T5, TMEMPR T6
WHERE 	(( @ISCO_EMPR IS NOT NULL AND T1.CO_EMPR = @ISCO_EMPR) OR @ISCO_EMPR IS NULL)
		AND T1.FE_OPER >= @IDFE_INIC
		AND T1.FE_OPER <= @IDFE_FINA
		AND T2.ST_REPO = 'S'
		AND T1.CO_EMPR = T2.CO_EMPR
		AND T1.CO_SUBR = T2.CO_SUBR
		AND T2.CO_EMPR = T3.CO_EMPR
		AND T2.CO_RUBR = T3.CO_RUBR
		AND T3.CO_EMPR = T4.CO_EMPR
		AND T3.CO_ARUB = T4.CO_ARUB
		AND T1.CO_EMPR = T5.CO_EMPR
		AND T1.CO_CAJA = T5.CO_CAJA
		AND T1.CO_EMPR = T6.CO_EMPR
		AND EXISTS ( SELECT T7.CO_RUBR
		 		FROM 	TTRUBR_FINA T7
		 		WHERE 	T7.CO_EMPR = T2.CO_EMPR
		 			AND T7.CO_RUBR = T2.CO_RUBR
		 			AND T7.ST_REPO = 'S'
		 AND 	EXISTS ( SELECT 	T8.CO_ARUB
		 	      	FROM    	TTARUB_FINA T8
		 	      	WHERE 	T8.CO_EMPR = T7.CO_EMPR
		 	      		AND T8.CO_ARUB = T7.CO_ARUB
		 	      		AND T8.ST_REPO = 'S'))
Union All
SELECT  T1.CO_EMPR , T4.CO_ARUB , T3.CO_RUBR , T2.CO_SUBR , T1.FE_OPER , T1.FA_TIPO_CAMB , T5.CO_MONE ,
	(T7.IM_DETA_NEGA*T8.PO_DIST)/100 ,  'B'	, T1.NU_COMP_BANC, T1.DE_OBSE
FROM	TCMOVI_BANC T1, TTSUBR_FINA T2, TTRUBR_FINA T3, TTARUB_FINA T4, TMCNTA_BANC T5, TMEMPR T6,
        TDMOVI_BANC T7, TDDISC_CCOS T8
WHERE 	(( @ISCO_EMPR IS NOT NULL AND T1.CO_EMPR = @ISCO_EMPR) OR @ISCO_EMPR IS NULL)
AND     T1.TI_MOVI_BANC = 'I'
AND     T1.FE_OPER >= @IDFE_INIC
AND     T1.FE_OPER <= @IDFE_FINA
AND	T1.TI_SITU != 'ANU'
AND 	T1.CO_EMPR = T5.CO_EMPR
AND 	T1.CO_BANC = T5.CO_BANC
AND 	T1.NU_CNTA_BANC = T5.NU_CNTA_BANC
AND 	T1.CO_EMPR = T6.CO_EMPR
AND     T1.CO_EMPR = T7.CO_EMPR
AND     T1.CO_UNID_CONC = T7.CO_UNID_CONC
AND     T1.CO_BANC = T7.CO_BANC
AND     T1.NU_CNTA_BANC = T7.NU_CNTA_BANC
AND     T1.AA_BNCO = T7.AA_BNCO
AND     T1.MM_BNCO = T7.MM_BNCO
AND     T1.TI_MOVI_BANC = T7.TI_MOVI_BANC
AND     T1.NU_COMP_BANC = T7.NU_COMP_BANC
AND     T7.CO_EMPR = T8.CO_EMPR
AND     T7.TI_DOCU_ENTI = T8.CO_TIPO_DOCU
AND     T7.NU_DOCU_ENTI = T8.NU_DOCU_CLIE
AND	T2.CO_EMPR = T8.CO_EMPR
AND	T2.CO_SUBR = T8.CO_SUBR
AND	T2.ST_REPO = 'S'
AND	T2.CO_EMPR = T3.CO_EMPR
AND 	T2.CO_RUBR = T3.CO_RUBR
AND	T3.CO_EMPR = T4.CO_EMPR
AND	T3.CO_ARUB = T4.CO_ARUB
AND 	EXISTS ( SELECT T7.CO_RUBR
		 FROM 	TTRUBR_FINA T7
		 WHERE 	T7.CO_EMPR = T2.CO_EMPR
		 AND 	T7.CO_RUBR = T2.CO_RUBR
		 AND 	T7.ST_REPO = 'S'
		 AND EXISTS ( SELECT T8.CO_ARUB
		 	      FROM    TTARUB_FINA T8
		 	      WHERE T8.CO_EMPR = T7.CO_EMPR
		 	      AND 	     T8.CO_ARUB = T7.CO_ARUB
		 	      AND 	     T8.ST_REPO = 'S'))
Union All
Select  T1.CO_EMPR , T4.CO_ARUB , T3.CO_RUBR , T2.CO_SUBR , T1.FE_OPER , T1.FA_TIPO_CAMB , T5.CO_MONE ,
	(T7.IM_DETA_NEGA*T8.PO_DIST)/100 ,  'C'	, T1.NU_COMP_CAJA, T1.DE_OBSE
FROM	TCMOVI_CAJA T1, TTSUBR_FINA T2, TTRUBR_FINA T3, TTARUB_FINA T4, TMCAJA T5, TMEMPR T6,
        TDMOVI_CAJA T7, TDDISC_CCOS T8
WHERE 	(( @ISCO_EMPR IS NOT NULL AND T1.CO_EMPR = @ISCO_EMPR) OR @ISCO_EMPR IS NULL)
AND     T1.TI_MOVI = 'I'
AND     T1.FE_OPER >= @IDFE_INIC
AND     T1.FE_OPER <= @IDFE_FINA
AND	T1.TI_SITU != 'ANU'
AND 	T1.CO_EMPR = T5.CO_EMPR
AND 	T1.CO_CAJA = T5.CO_CAJA
AND 	T1.CO_EMPR = T6.CO_EMPR
AND     T1.CO_EMPR = T7.CO_EMPR
AND     T1.CO_CAJA = T7.CO_CAJA
AND     T1.AA_CAJA = T7.AA_CAJA
AND     T1.MM_CAJA = T7.MM_CAJA
AND     T1.TI_MOVI = T7.TI_MOVI
AND     T1.NU_COMP_CAJA = T7.NU_COMP_CAJA
AND     T7.CO_EMPR = T8.CO_EMPR
AND     T7.TI_DOCU_ENTI = T8.CO_TIPO_DOCU
AND     T7.NU_DOCU_ENTI = T8.NU_DOCU_CLIE
AND	T2.CO_EMPR = T8.CO_EMPR
AND	T2.CO_SUBR = T8.CO_SUBR
AND	T2.ST_REPO = 'S'
AND	T2.CO_EMPR = T3.CO_EMPR
AND 	T2.CO_RUBR = T3.CO_RUBR
AND	T3.CO_EMPR = T4.CO_EMPR
AND	T3.CO_ARUB = T4.CO_ARUB
AND 	EXISTS ( SELECT T7.CO_RUBR
		 FROM 	TTRUBR_FINA T7
		 WHERE 	T7.CO_EMPR = T2.CO_EMPR
		 AND 	T7.CO_RUBR = T2.CO_RUBR
		 AND 	T7.ST_REPO = 'S'
		 AND EXISTS ( SELECT T8.CO_ARUB
		 	      FROM    TTARUB_FINA T8
		 	      WHERE T8.CO_EMPR = T7.CO_EMPR
		 	      AND 	     T8.CO_ARUB = T7.CO_ARUB
		 	      AND 	     T8.ST_REPO = 'S'))
/* Caso de las Letras */

DECLARE CU_TCMOVI_BANC_Q18_1 Cursor 
For
Select  T1.CO_EMPR , T6.DE_NOMB , 'B' ,
	T1.NU_COMP_BANC , T1.FE_OPER , T1.DE_OBSE , T5.CO_MONE , T1.FA_TIPO_CAMB ,
	T7.IM_DETA_NEGA , T1.TI_SITU , T8.NU_CANJ, T8.NU_RENO, T7.CO_ENTI
FROM	TCMOVI_BANC T1, TMCNTA_BANC T5, TMEMPR T6,  TDMOVI_BANC T7,
        TMLETR_CLIE T8        
WHERE 	(( @ISCO_EMPR IS NOT NULL AND T1.CO_EMPR = @ISCO_EMPR) OR @ISCO_EMPR IS NULL)
AND     T1.TI_MOVI_BANC = 'I'
AND     T1.FE_OPER >= @IDFE_INIC
AND     T1.FE_OPER <= @IDFE_FINA
AND	    T1.TI_SITU != 'ANU'
AND 	T1.CO_EMPR = T5.CO_EMPR
AND 	T1.CO_BANC = T5.CO_BANC
AND 	T1.NU_CNTA_BANC = T5.NU_CNTA_BANC
AND 	T1.CO_EMPR = T6.CO_EMPR
AND     T1.CO_EMPR = T7.CO_EMPR
AND     T1.CO_UNID_CONC = T7.CO_UNID_CONC
AND     T1.CO_BANC = T7.CO_BANC
AND     T1.NU_CNTA_BANC = T7.NU_CNTA_BANC
AND     T1.AA_BNCO = T7.AA_BNCO
AND     T1.MM_BNCO = T7.MM_BNCO
AND     T1.TI_MOVI_BANC = T7.TI_MOVI_BANC
AND     T1.NU_COMP_BANC = T7.NU_COMP_BANC
AND     T7.CO_EMPR = T8.CO_EMPR
AND     T7.TI_DOCU_ENTI = 'LET'
AND     T7.NU_DOCU_ENTI = T8.NU_LETR_CLIE
Union All
Select  T1.CO_EMPR , T6.DE_NOMB , 'C' ,
	T1.NU_COMP_CAJA , T1.FE_OPER , T1.DE_OBSE , T5.CO_MONE , T1.FA_TIPO_CAMB ,
	T7.IM_DETA_NEGA , T1.TI_SITU , T8.NU_CANJ, T8.NU_RENO, T7.CO_ENTI
FROM	TCMOVI_CAJA T1, TMCAJA T5, TMEMPR T6,  TDMOVI_CAJA T7,
        TMLETR_CLIE T8        
WHERE 	(( @ISCO_EMPR IS NOT NULL AND T1.CO_EMPR = @ISCO_EMPR) OR @ISCO_EMPR IS NULL)
AND     T1.TI_MOVI = 'I'
AND     T1.FE_OPER >= @IDFE_INIC
AND     T1.FE_OPER <= @IDFE_FINA
AND	T1.TI_SITU != 'ANU'
AND 	T1.CO_EMPR = T5.CO_EMPR
AND 	T1.CO_CAJA = T5.CO_CAJA
AND 	T1.CO_EMPR = T6.CO_EMPR
AND     T1.CO_EMPR = T7.CO_EMPR
AND     T1.CO_CAJA = T7.CO_CAJA
AND     T1.AA_CAJA = T7.AA_CAJA
AND     T1.MM_CAJA = T7.MM_CAJA
AND     T1.TI_MOVI = T7.TI_MOVI
AND     T1.NU_COMP_CAJA = T7.NU_COMP_CAJA
AND     T7.CO_EMPR = T8.CO_EMPR
AND     T7.TI_DOCU_ENTI = 'LET'
AND     T7.NU_DOCU_ENTI = T8.NU_LETR_CLIE

Open   CU_TCMOVI_BANC_Q18_1
Fetch  CU_TCMOVI_BANC_Q18_1 INTO  @CSCO_EMPR , @CSDE_NOMB , @CSTI_TRAN ,
       @CSNU_COMP_BANC , @CDFE_OPER , @CSDE_OBSE , @CSCO_MONE , @CNFA_TIPO_CAMB ,
       @CNIM_DETA_NEGA , @CSTI_SITU , @CSNU_CANJ, @CSNU_RENO, @CSCO_CLIE

 Select @VNFE_0001  = @@FETCH_STATUS

 While (@VNFE_0001 = 0)
   Begin
     If (@VNFE_0001 < 0)
         Begin     
 	   Close CU_TCMOVI_BANC_Q18_1
	   Deallocate CU_TCMOVI_BANC_Q18_1
	   Return
         End		

        If @CSNU_RENO is not null
           Begin
             Select @CSNU_LETR_CLIE = NU_DOCU_CLIE
             From   TDRENO_CLIE
             Where  CO_EMPR = @CSCO_EMPR
             And    CO_CLIE = @CSCO_CLIE
             And    NU_RENO = @CSNU_RENO              
	     And    NU_DOCU_CLIE is not null
 
             Select @CSNU_CANJ = NU_CANJ
             From   TMLETR_CLIE
             Where  CO_EMPR = @CSCO_EMPR
             And    NU_LETR_CLIE = @CSNU_LETR_CLIE
           End             

        If @CSNU_CANJ is not null
           Begin 
	     DECLARE CU_TCMOVI_BANC_Q18_2 Cursor 
	     For
             Select CO_TIPO_DOCU, NU_DOCU_CLIE
             From   TDCANJ_CLIE
             Where  CO_EMPR = @CSCO_EMPR
             And    CO_CLIE = @CSCO_CLIE
             And    NU_CANJ = @CSNU_CANJ        
             And    CO_TIPO_DOCU is not null
 
             Open   CU_TCMOVI_BANC_Q18_2
             Fetch  CU_TCMOVI_BANC_Q18_2 INTO  @CSCO_TIPO_DOCU, @CSNU_DOCU_CLIE

          Select @VNFE_0002  = @@FETCH_STATUS

             While (@VNFE_0002 = 0)
               Begin
                 If (@VNFE_0002 < 0)
                    Begin     
 	              Close CU_TCMOVI_BANC_Q18_2
	              Deallocate CU_TCMOVI_BANC_Q18_2
	              Return
                    End		

                INSERT INTO #TWFLUJ_CAJA_Q01_2
                (CO_EMPR, CO_ARUB, CO_RUBR, CO_SUBR, FE_OPER, FA_TIPO_CAMB, CO_MONE, IM_MOVI, TI_DETA, NU_COMP_TRAN, 
                 TI_SITU)
                SELECT @CSCO_EMPR, T4.CO_ARUB , T3.CO_RUBR ,
		       T2.CO_SUBR , @CDFE_OPER, @CNFA_TIPO_CAMB, @CSCO_MONE, 
	               (@CNIM_DETA_NEGA*T1.PO_DIST )/100, @CSTI_TRAN, @CSNU_COMP_BANC, @CSTI_SITU
                FROM TDDISC_CCOS T1, TTSUBR_FINA T2, TTRUBR_FINA T3, TTARUB_FINA T4
                Where T1.CO_EMPR = @CSCO_EMPR
                AND   T1.CO_TIPO_DOCU = @CSCO_TIPO_DOCU
                AND   T1.NU_DOCU_CLIE = @CSNU_DOCU_CLIE
	        AND   T2.CO_EMPR = T1.CO_EMPR
	        AND   T2.CO_SUBR = T1.CO_SUBR_LETR
	        AND   T2.ST_REPO = 'S'
	        AND   T2.CO_EMPR = T3.CO_EMPR
	        AND   T2.CO_RUBR = T3.CO_RUBR
	        AND   T3.CO_EMPR = T4.CO_EMPR
	        AND   T3.CO_ARUB = T4.CO_ARUB
	        AND   EXISTS ( SELECT T7.CO_RUBR
		FROM 	TTRUBR_FINA T7
		WHERE 	T7.CO_EMPR = T2.CO_EMPR
		AND 	T7.CO_RUBR = T2.CO_RUBR
		AND 	T7.ST_REPO = 'S'
		AND EXISTS ( SELECT T8.CO_ARUB
		 	      FROM    TTARUB_FINA T8
		 	      WHERE T8.CO_EMPR = T7.CO_EMPR
		 	      AND 	     T8.CO_ARUB = T7.CO_ARUB
		 	      AND 	     T8.ST_REPO = 'S'))

               Fetch  CU_TCMOVI_BANC_Q18_2 INTO  @CSCO_TIPO_DOCU, @CSNU_DOCU_CLIE

              Select @VNFE_0002 = @@FETCH_STATUS
         End


        Close CU_TCMOVI_BANC_Q18_2
        Deallocate CU_TCMOVI_BANC_Q18_2
      End
          
     Fetch  CU_TCMOVI_BANC_Q18_1 INTO  @CSCO_EMPR , @CSDE_NOMB , @CSTI_TRAN ,
     @CSNU_COMP_BANC , @CDFE_OPER , @CSDE_OBSE , @CSCO_MONE , @CNFA_TIPO_CAMB ,
     @CNIM_DETA_NEGA , @CSTI_SITU , @CSNU_CANJ, @CSNU_RENO, @CSCO_CLIE

     Select @VNFE_0001 = @@FETCH_STATUS
   End

 Close CU_TCMOVI_BANC_Q18_1
 Deallocate CU_TCMOVI_BANC_Q18_1

IF @ISCO_MONE = 'DOL'
   Begin
     UPDATE #TWFLUJ_CAJA_Q01_2
     SET IM_MOVI_CONV = IM_MOVI / FA_TIPO_CAMB
     WHERE CO_MONE != @ISCO_MONE

     UPDATE #TWFLUJ_CAJA_Q01_2
     SET IM_MOVI_CONV = IM_MOVI 
     WHERE CO_MONE = @ISCO_MONE

   End
ELSE
  Begin
    UPDATE #TWFLUJ_CAJA_Q01_2
    SET IM_MOVI_CONV = IM_MOVI * FA_TIPO_CAMB
    WHERE CO_MONE != @ISCO_MONE

    UPDATE #TWFLUJ_CAJA_Q01_2
    SET IM_MOVI_CONV = IM_MOVI 
    WHERE CO_MONE = @ISCO_MONE

  End


SELECT T1.CO_EMPR, T5.DE_NOMB, T4.CO_ARUB, T4.DE_ARUB, T3.CO_RUBR, T3.DE_RUBR, 
T2.CO_SUBR, T2.DE_SUBR, T1.TI_DETA , T1.NU_COMP_TRAN, T1.FE_OPER, T1.DE_OBSE, T1.CO_MONE,
T1.FA_TIPO_CAMB , IM_MOVI, IM_MOVI_CONV, T1.TI_SITU
FROM #TWFLUJ_CAJA_Q01_2 T1, TTSUBR_FINA T2, TTRUBR_FINA T3, TTARUB_FINA T4, TMEMPR T5
WHERE 	T1.CO_SUBR = T2.CO_SUBR
 AND 	T2.ST_REPO = 'S'
AND  	T1.CO_RUBR = T3.CO_RUBR
AND  	T1.CO_ARUB = T4.CO_ARUB
AND	T1.CO_EMPR = T5.CO_EMPR
AND 	EXISTS ( SELECT T6.CO_RUBR
		 FROM 	TTRUBR_FINA T6
		 WHERE 	T6.CO_EMPR = T2.CO_EMPR
		 AND 	T6.CO_RUBR = T2.CO_RUBR
		 AND 	T6.ST_REPO = 'S'
		 AND EXISTS ( SELECT T7.CO_ARUB
		 	      FROM 	TTARUB_FINA T7
		 	      WHERE 	T7.CO_EMPR = T6.CO_EMPR
		 	      AND 	T7.CO_ARUB = T6.CO_ARUB
		 	      AND 	T7.ST_REPO = 'S'))
ORDER BY T1.CO_EMPR, T4.NU_ORDE, T3.NU_ORDE, T2.NU_ORDE

RETURN


GO
